﻿CREATE PROCEDURE [dbo].[proc_Orders_Department_Count1]
	(
		@FixtureDate nvarchar (50),
		@DepartmentId int
	)
AS
--	;WITH ChildDepartment(ID,DName,PId) AS
--	(
--		SELECT ID,DName,ParentID FROM department WHERE ParentID=@departmentid or id=@departmentid
--	    
--		UNION ALL
--		SELECT department.ID,department.DName,department.ParentID FROM ChildDepartment AS d
--		INNER JOIN department ON d.ID = department.ParentID
--	)
	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@DepartmentId

	select count(id) from orders 
	where (select e.departmentid from employee e where e.username=Orders.UserName) in(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T) 
          and Convert(varchar(7),FixtureDate,120)>=@FixtureDate
          and FinanceFlag = 1
         
	RETURN
